CREATE TABLE [dbo].[tb_Usuarios]
(
[idUsuario] [int] NOT NULL IDENTITY(1, 1),
[Nombre] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Email] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Clave] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Usuario] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NTUser] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Activo] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[esJefe] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[idReparticion] [int] NULL,
[Nivel] [int] NULL,
[Modo] [int] NOT NULL,
[Dependencia] [int] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[delete_usuario] ON [dbo].[tb_Usuarios]
FOR DELETE 
AS
SET NOCOUNT ON
DELETE tb_RelUsuarioGrupo from tb_RelUsuarioGrupo WHERE idUsuario in (SELECT idUsuario FROM deleted)
DELETE tb_RelUsuarioRol FROM tb_RelUsuarioRol WHERE idUsuario in (SELECT idUsuario FROM deleted)
DELETE tb_RelUsuarioPermisos FROM tb_RelUsuarioPermisos WHERE idUsuario in (SELECT idUsuario FROM deleted)

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[updateUsuario] ON [dbo].[tb_Usuarios] 
FOR UPDATE
AS
SET NOCOUNT ON
if (update(idUsuario)) begin
	UPDATE tb_Inspecciones
	SET Supervisor = (SELECT idUsuario from inserted)
	WHERE Supervisor in (SELECT idUsuario from deleted)

	UPDATE tb_Reparaciones
	SET Supervisor = (SELECT idUsuario from inserted)
	WHERE Supervisor in (SELECT idUsuario from deleted)

	UPDATE tb_RelUsuarioGrupo
	SET idUsuario = (SELECT idUsuario from inserted)
	WHERE idUsuario in (SELECT idUsuario from deleted)

	UPDATE tb_RelUsuarioPermisos
	SET idUsuario = (SELECT idUsuario from inserted)
	WHERE idUsuario in (SELECT idUsuario from deleted)
	
	UPDATE tb_RelUsuarioRol
	SET idUsuario = (SELECT idUsuario from inserted)
	WHERE idUsuario in (SELECT idUsuario from deleted)

end

GO
ALTER TABLE [dbo].[tb_Usuarios] ADD CONSTRAINT [PK_tb_Usuarios] PRIMARY KEY CLUSTERED  ([idUsuario]) WITH (FILLFACTOR=100) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idxNtUserUsuarios] ON [dbo].[tb_Usuarios] ([NTUser]) WITH (FILLFACTOR=100) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_Usuario] ON [dbo].[tb_Usuarios] ([Usuario]) WITH (FILLFACTOR=100) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_Usuarios] WITH NOCHECK ADD CONSTRAINT [FK_tb_Usuarios_tb_Reparticiones] FOREIGN KEY ([idReparticion]) REFERENCES [dbo].[tb_Reparticiones] ([idReparticion]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[tb_Usuarios] WITH NOCHECK ADD CONSTRAINT [FK_tb_Usuarios_tb_NivelesEnsenanza] FOREIGN KEY ([Nivel]) REFERENCES [dbo].[tb_NivelesEnsenanza] ([idNivelEnsenanza])
GO
ALTER TABLE [dbo].[tb_Usuarios] NOCHECK CONSTRAINT [FK_tb_Usuarios_tb_Reparticiones]
GO
GRANT SELECT ON  [dbo].[tb_Usuarios] TO [public]
GO
